"""
自动创建套餐相关数据库表的脚本
运行方式：python setup_package_tables.py
"""
import os
import sys
from pathlib import Path

# 添加项目路径到Python路径
sys.path.insert(0, str(Path(__file__).parent))

try:
    from sqlalchemy import create_engine, text
    from app.config import settings
    print("✓ 成功导入配置")
except ImportError as e:
    print(f"✗ 导入失败: {e}")
    print("\n请确保已安装依赖: pip install sqlalchemy pymysql")
    sys.exit(1)


def read_sql_file():
    """读取SQL文件"""
    sql_file = Path(__file__).parent / "create_package_tables.sql"
    if not sql_file.exists():
        print(f"✗ SQL文件不存在: {sql_file}")
        return None
    
    with open(sql_file, 'r', encoding='utf-8') as f:
        return f.read()


def execute_sql(engine, sql_content):
    """执行SQL语句"""
    # 分割SQL语句（按分号分割）
    statements = []
    current_statement = []
    
    for line in sql_content.split('\n'):
        # 跳过注释行
        stripped = line.strip()
        if stripped.startswith('--') or not stripped:
            continue
        
        current_statement.append(line)
        
        # 如果行以分号结尾，表示一条SQL语句结束
        if stripped.endswith(';'):
            statements.append('\n'.join(current_statement))
            current_statement = []
    
    # 执行SQL语句
    success_count = 0
    error_count = 0
    
    with engine.connect() as conn:
        for i, statement in enumerate(statements, 1):
            if not statement.strip():
                continue
            
            try:
                conn.execute(text(statement))
                conn.commit()
                success_count += 1
                
                # 显示进度
                if 'CREATE TABLE' in statement:
                    table_name = statement.split('`')[1]
                    print(f"  ✓ 创建表: {table_name}")
                elif 'INSERT INTO' in statement:
                    table_name = statement.split('`')[1]
                    print(f"  ✓ 插入数据到: {table_name}")
                
            except Exception as e:
                error_count += 1
                print(f"  ✗ 执行失败 (语句 {i}): {str(e)[:100]}")
                # 继续执行后续语句
                continue
    
    return success_count, error_count


def verify_tables(engine):
    """验证表是否创建成功"""
    tables_to_check = [
        'service_packages',
        'custom_packages',
        'package_features',
        'faqs'
    ]
    
    print("\n" + "="*50)
    print("验证表创建情况：")
    print("="*50)
    
    with engine.connect() as conn:
        all_success = True
        for table in tables_to_check:
            try:
                result = conn.execute(text(f"SELECT COUNT(*) as count FROM `{table}`"))
                count = result.fetchone()[0]
                print(f"  ✓ {table}: {count} 条记录")
            except Exception as e:
                print(f"  ✗ {table}: 不存在或查询失败")
                all_success = False
    
    return all_success


def main():
    """主函数"""
    print("="*50)
    print("套餐管理数据库表创建工具")
    print("="*50)
    print()
    
    # 1. 读取SQL文件
    print("Step 1: 读取SQL文件...")
    sql_content = read_sql_file()
    if not sql_content:
        sys.exit(1)
    print("  ✓ SQL文件读取成功")
    
    # 2. 连接数据库
    print("\nStep 2: 连接数据库...")
    try:
        # 获取数据库URL
        if hasattr(settings, 'DATABASE_URL'):
            db_url = settings.DATABASE_URL
        else:
            # 构建数据库URL
            db_url = f"mysql+pymysql://{settings.MYSQL_USER}:{settings.MYSQL_PASSWORD}@{settings.MYSQL_HOST}:{settings.MYSQL_PORT}/{settings.MYSQL_DATABASE}"
        
        engine = create_engine(db_url, echo=False)
        
        # 测试连接
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        
        print("  ✓ 数据库连接成功")
    except Exception as e:
        print(f"  ✗ 数据库连接失败: {e}")
        print("\n请检查以下配置：")
        print("  - app/config.py 中的数据库配置")
        print("  - 数据库服务是否启动")
        print("  - 数据库用户名密码是否正确")
        sys.exit(1)
    
    # 3. 执行SQL
    print("\nStep 3: 执行SQL语句...")
    print("-" * 50)
    success_count, error_count = execute_sql(engine, sql_content)
    print("-" * 50)
    print(f"  成功: {success_count} 条, 失败: {error_count} 条")
    
    # 4. 验证
    print("\nStep 4: 验证表创建...")
    all_success = verify_tables(engine)
    
    # 5. 完成
    print("\n" + "="*50)
    if all_success and error_count == 0:
        print("✓ 所有表创建成功！")
        print("\n下一步：")
        print("  1. 重启后端服务")
        print("  2. 访问管理后台测试功能")
        print("  3. 路径：套餐管理 → 各个子菜单")
    else:
        print("⚠ 部分操作失败，请查看上方错误信息")
        print("\n可能的解决方案：")
        print("  1. 检查数据库用户权限")
        print("  2. 手动执行 create_package_tables.sql")
        print("  3. 查看详细错误信息解决问题")
    print("="*50)


if __name__ == "__main__":
    try:
        main()
    except KeyboardInterrupt:
        print("\n\n操作已取消")
        sys.exit(0)
    except Exception as e:
        print(f"\n✗ 发生未预期的错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

